const express = require('express')
const bodyParser = require('body-parser')
const { format } = require('date-fns')
const moment = require('moment')
const cors = require('cors')
const mysql = require('mysql')
const jwt = require('jsonwebtoken')
const app = express()

const { menuList } = require('../data/menu')
const { isEmptyObj } = require('../data/method')
app.use(bodyParser.json())
app.use(cors())
app.use(bodyParser.urlencoded({ extended: true }))

// 设置统一的JSON响应格式
function createResponse(code, msg, data) {
  return {
    code,
    msg,
    data
  }
}

// 密钥，用于签名token
const secretKey = 'cocoxuan'
// 结合MySQL数据库
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '1165094065',
  database: 'attendance'
})

connection.connect(err => {
  if (err) {
    console.error('Error connecting to MySQL database: ' + err.stack)
    return
  }
})
//course_prevent_always 表名
// connection.query("SELECT * FROM user", (err, results, fields) => {
//   if (err) throw err;
//   // 定义POST请求的路由
// app.get("/api", (req, res) => {
//   //   res.send(results);
//   // });

//   // 启动服务器

//   // 定义GET请求的路由

// });

// 引入路由

// require('../routers/login')(app, connection)

app.post('/login', (req, res) => {
  const { username: phone, password } = req.body // 从请求体中获取用户名和密码
  // 查询数据库中的用户表
  connection.query('SELECT * FROM user WHERE phone = ? AND password = ?', [phone, password], (err, results) => {
    if (err) {
      return res.status(500).send('Internal Server Error')
    }
    if (results.length > 0) {
      // 用户存在，登录成功
      // console.log(results, '1111')

      let { type } = results[0]

      let sql
      // 这里有问题
      type === 1 ? (sql = `SELECT *,class.classId,class.className FROM user LEFT JOIN class ON user.id = class.id WHERE id = ${[results[0].id]}`) : (sql = `SELECT *,class.className FROM user LEFT JOIN class ON user.classId = class.classId WHERE id = ${[results[0].id]}`)
      console.log(sql, '1', type)
      if (type === 0) {
        const accessToken = jwt.sign({ user: results[0] }, secretKey, { expiresIn: '24h' })
        return res.send(createResponse(200, '登录成功', { accessToken }))
      } else {
        connection.query(sql, (err, result) => {
          if (result && result.length) {
            results[0] = result[0]
          }
          console.log(results[0], '这里才是用户信息')
          const accessToken = jwt.sign({ user: results[0] }, secretKey, { expiresIn: '24h' })
          return res.send(createResponse(200, '登录成功', { accessToken }))
        })
      }

      // 生成JWT
    } else {
      // 用户不存在或密码错误
      connection.query('SELECT * FROM user WHERE phone = ?', [phone], (err, results) => {
        if (results.length > 0) {
          return res.status(401).json(createResponse(401, '密码不正确'))
        } else {
          return res.status(401).json(createResponse(401, '用户不存在'))
        }
      })
    }
  })
})
// 验证token的中间件
const authenticateToken = async (req, res, next) => {
  const authHeader = req.headers['authorization']
  const token = authHeader && authHeader.split(' ')[1]
  if (token == null) {
    return res.sendStatus(401)
  }
  jwt.verify(token, secretKey, async (err, user) => {
    if (err) {
      return res.sendStatus(403)
    }
    console.log(user, '111')
    // 如果想在请求中添加用户信息，可以将其附加到req对象上
    user.user.roles = JSON.parse(user.user.roles)
    req.user = user.user
    next()
  })
}

// 获取用户信息
app.get('/api/v1/users/me', authenticateToken, (req, res) => {
  let { classId, id, type } = req.user
  let data = req.user
  delete data.password
  return res.send(createResponse(200, '', data))
})

// 获取用户路由
app.get('/api/v1/menus/routes', authenticateToken, (req, res) => {
  // let data = req.user.roles
  return res.send(createResponse(200, '', menuList))
})
// 获取班级列表
app.get('/api/v1/class', authenticateToken, (req, res) => {
  let query = 'SELECT class.className,class.classId,user.username,user.id ' + 'FROM class ' + 'LEFT JOIN user ON class.id = user.id'
  connection.query(query, function (error, results, fields) {
    if (error) throw error
    if (!isEmptyObj(req.query)) {
      const { className } = req.query
      let query = 'SELECT * FROM `class`'
      let params = []

      // 检查是否提供了 className 参数
      if (className) {
        query += ' WHERE `className` = ?'
        params.push(className)
      }
      connection.query(query, params, function (error, results, fields) {
        if (error) {
          // 如果有错误，返回错误信息
          return res.status(500).json({ error: 'Error fetching classes' })
        }

        // 如果没有错误，返回结果
        return res.status(200).json(createResponse(200, '', results))
      })
    } else {
      return res.send(createResponse(200, '', results))
    }
  })
})
// 新增班级列表
app.post('/api/v1/class', authenticateToken, async (req, res) => {
  const { className, id: userId } = req.body

  // 校验名称是否已存在
  connection.query('SELECT * FROM `class` WHERE `className` = ?', [className], function (error, results, fields) {
    if (results.length > 0) {
      return res.send(createResponse(500, '名称重复'))
    }
    // 插入新数据到class表
    const query = 'INSERT INTO class (className,id) VALUES (?, ?)'
    connection.query(query, [className, userId], (err, result) => {
      if (err) {
        return res.status(500).json({ error: 'Failed to add class' })
      }
      let classId = result.insertId
      let sql1 = 'UPDATE user SET classId = ? WHERE id = ?'
      connection.query(sql1, [classId, userId], (err, result) => {
        return res.send(createResponse(200, '添加成功'))
      })
    })
  })
})
// 修改班级名称
app.put('/api/v1/class/:classId', authenticateToken, async (req, res) => {
  const { classId } = req.params
  const { className, id: userId } = req.body

  // 检查 classId 和 className 是否存在
  if (!classId || !className) {
    return res.status(400).json({ error: 'Missing classId or className in request body' })
  }

  // 校验名称是否已存在（除了当前班级）
  connection.query('SELECT * FROM `class` WHERE `className` = ? AND `classId` <> ?', [className, classId], function (error, results, fields) {
    if (error) {
      return res.status(500).json({ error: 'Error occurred while checking class existence' })
    }
    if (results.length > 0) {
      return res.send(createResponse(500, '名称重复'))
    }

    // 更新class表中的数据
    const query = 'UPDATE class SET className = ?, id = ? WHERE classId = ?'

    connection.query(query, [className, userId, classId], (err, result) => {
      if (err) {
        return res.status(500).json({ error: 'Failed to update class' })
      }
      if (result.affectedRows === 0) {
        return res.send(createResponse(404, 'Class not found'))
      }
      let sql1 = 'UPDATE user SET classId = ? WHERE id = ?'
      connection.query(sql1, [classId, userId], (err, result) => {
        return res.send(createResponse(200, 'Class updated successfully'))
      })
    })
  })
})
// 批量删除班级
app.delete('/api/v1/class/:classId', authenticateToken, async (req, res) => {
  const { classId } = req.params
  let classIds = classId.split(',')
  // 检查 classIds 是否为有效的非空数组
  if (!Array.isArray(classIds) || classIds.length === 0) {
    return res.status(400).json({ error: '请提供至少一个有效的 classId' })
  }
  console.log(classIds)
  // 使用 IN 语句来匹配多个 classId
  const query = 'DELETE FROM class WHERE classId IN (?) AND NOT EXISTS (SELECT 1 FROM user WHERE user.classId = class.classId  AND user.type = 2)'
  // 为了防止SQL注入，我们将使用连接对象的 query 方法来安全地处理数组
  console.log(connection.format(query, [classIds]))
  connection.query(query, [classIds], (err, result) => {
    if (err) {
      return res.status(500).json({ error: '删除班级时出错' })
    }
    if (result.affectedRows === 0) {
      return res.send(createResponse(500, '班级下有学生不可删除'))
    }
    let sql1 = 'UPDATE user SET classId = NULL WHERE classId = ? AND type = 1'
    connection.query(sql1, [classIds], (err, result) => {
      return res.send(createResponse(200, `${result.affectedRows} 个班级删除成功`))
    })
  })
})

// 获取用户列表
app.get('/api/v1/teachers', authenticateToken, (req, res) => {
  const { classId, username, type } = req.query
  let sql = 'SELECT user.id, user.username, user.roles, user.classId, user.type, user.phone, user.sex, user.serial, user.subject, user.email, class.className ' + 'FROM user ' + 'LEFT JOIN class ON user.classId = class.classId ' + `WHERE user.type = ${type}`
  let params = []
  // // 检查是否提供了 className 参数
  if (classId) {
    sql += ' AND `user`.`classId` = ?'
    params.push(classId)
  }
  if (username) {
    sql += ' AND  `username` = ?'
    params.push(username)
  }
  connection.query(sql, params, function (error, results, fields) {
    if (error) {
      // 如果有错误，返回错误信息
      return res.status(500).json({ error: 'Error fetching classes' })
    }
    return res.status(200).json(createResponse(200, '', results))
  })
})
// 生成学号的函数

// 创建用户
app.post('/api/v1/teachers', async (req, res) => {
  const { username, sex, phone, type, subject, email, classId } = req.body
  const roles = [type === 1 ? 'TEACHER' : 'STUDENT'] // 自动为用户角色赋值
  if (!username || !phone) {
    return res.status(400).json({ msg: '请提供完整的信息' })
  }
  connection.query('SELECT * FROM `user` WHERE `phone` = ?', [phone], async function (error, results, fields) {
    if (results.length > 0) {
      return res.send(createResponse(500, '该账号已存在'))
    }
    let str = phone.toString()
    // const password = `cocoxuan${str.substr(str.length - 4, str.length)}`
    const password = '123456'

    let sql1 = 'SELECT * FROM user WHERE type = ?'
    connection.query(sql1, [type], (err, result) => {
      console.log(result.length, '111111')
      let number = result.length
      number += 1
      let formattedNumber = '2024' + number.toString().padStart(4, '0') // 将当前的编号格式化为八位数
      type === 1 ? (formattedNumber = `T${formattedNumber}`) : (formattedNumber = `S${formattedNumber}`)
      const sql = 'INSERT INTO user (username, password, phone, roles, type,serial,sex,subject,email,classId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
      const values = [username, password, phone, JSON.stringify(roles), type, formattedNumber, sex, subject, email, classId]
      connection.query(sql, values, (err, result) => {
        if (err) throw err
        return res.send(createResponse(200, '', result))
      })
    })
  })
})

// // 更新用户信息
app.put('/api/v1/teachers/:id', async (req, res) => {
  const { id } = req.params
  const { username, password, classId, phone, type, email, sex, subject } = req.body
  let sql
  let values = []
  let idSql = 'SELECT * FROM user WHERE id=?'
  connection.query(idSql, [id], (error, result) => {
    console.log(res, '这里是全部信息')
    let { phone: user_phone } = result[0]
    console.log(user_phone, '打印一下电话吧')

    if (user_phone !== phone) {
      connection.query('SELECT * FROM `user` WHERE `phone` = ?', [phone], async function (error, results, fields) {
        if (results.length > 0) {
          return res.send(createResponse(500, '该账号已存在'))
        } else {
          if (password) {
            sql = `UPDATE user SET username = ?, password = ?, classId = ?, phone = ?, email = ?, sex = ?,subject = ? WHERE id = ?  AND type = ${type}`
            values = [username, password, classId, phone, email, sex, subject, id]
          } else {
            sql = `UPDATE user SET username = ?,  classId = ?, phone = ?, email = ?,sex = ?,subject = ? WHERE id = ? AND type = ${type}`
            values = [username, classId, phone, email, sex, subject, id]
          }

          connection.query(sql, values, (err, result) => {
            if (err) throw err
            if (result.affectedRows === 0) {
              return res.status(404).json({ error: '用户不存在' })
            }
            return res.send(createResponse(200, '', result))
          })
        }
      })
    } else {
      if (password) {
        sql = `UPDATE user SET username = ?, password = ?, classId = ?, phone = ?, email = ?, sex = ?,subject = ? WHERE id = ?  AND type = ${type}`
        values = [username, password, classId, phone, email, sex, subject, id]
      } else {
        sql = `UPDATE user SET username = ?,  classId = ?, phone = ?, email = ?,sex = ?,subject = ? WHERE id = ? AND type = ${type}`
        values = [username, classId, phone, email, sex, subject, id]
      }

      connection.query(sql, values, (err, result) => {
        if (err) throw err
        if (result.affectedRows === 0) {
          return res.status(404).json({ error: '用户不存在' })
        }
        return res.send(createResponse(200, '', result))
      })
    }
  })
})

// // 删除用户
app.delete('/api/v1/teachers/:id', async (req, res) => {
  const { id } = req.params
  let ids = id.split(',')
  // 检查 classIds 是否为有效的非空数组
  if (!Array.isArray(ids) || ids.length === 0) {
    return res.status(400).json({ error: '请提供至少一个有效的 id' })
  }
  // 使用 IN 语句来匹配多个 classId
  const query = 'DELETE FROM `user` WHERE `id` IN (?) AND type = 1'
  // 为了防止SQL注入，我们将使用连接对象的 query 方法来安全地处理数组
  connection.query(query, [ids], (err, result) => {
    if (err) {
      return res.status(500).json({ error: '删除用户时出错' })
    }
    if (result.affectedRows === 0) {
      return res.send(createResponse(404, '未找到要删除的用户'))
    }
    return res.send(createResponse(200, `删除成功`))
  })
})
// // 删除学生
app.delete('/api/v1/students/:id', async (req, res) => {
  const { id } = req.params
  let ids = id.split(',')
  // 检查 classIds 是否为有效的非空数组
  if (!Array.isArray(ids) || ids.length === 0) {
    return res.status(400).json({ error: '请提供至少一个有效的 id' })
  }
  console.log(ids, '11')
  // 使用 IN 语句来匹配多个 classId
  const query = 'DELETE FROM `user` WHERE `id` IN (?) AND type = 2'
  // 为了防止SQL注入，我们将使用连接对象的 query 方法来安全地处理数组
  connection.query(query, [ids], (err, result) => {
    if (err) {
      return res.status(500).json({ error: '删除用户时出错' })
    }
    if (result.affectedRows === 0) {
      return res.send(createResponse(404, '未找到要删除的用户'))
    }
    return res.send(createResponse(200, `删除成功`))
  })
})

// 获取班级下的用户（老师和学生）
app.get('/api/v1/users', authenticateToken, (req, res) => {
  const { classId } = req.query
  let sql = 'SELECT user.id, user.username, user.roles, user.classId,user.sex, user.type,user.email,user.serial, user.phone, class.className ' + 'FROM user ' + 'LEFT JOIN class ON user.classId = class.classId '
  let params = []
  // // 检查是否提供了 className 参数
  if (classId) {
    sql += ' WHERE `user`.`classId` = ?'
    params.push(classId)
  }
  sql += ' ORDER BY CASE WHEN user.type = 1 THEN 0 ELSE 1 END, user.type'
  connection.query(sql, params, function (error, results, fields) {
    if (error) {
      // 如果有错误，返回错误信息
      return res.status(500).json({ error: 'Error fetching classes' })
    }
    return res.status(200).json(createResponse(200, '', results))
  })
})

// 获取请假列表
app.get('/api/v1/leave', authenticateToken, (req, res) => {
  const { classId, username, type, leave_type } = req.query
  let sql = 'SELECT `leave`.*, `class`.className ' + 'FROM `leave` ' + 'LEFT JOIN `class` ON `leave`.classId = `class`.classId ' + 'WHERE `leave`.type = ?'

  let params = [type]
  // // 检查是否提供了 className 参数
  if (classId) {
    sql += ' AND `leave`.`classId` = ?'
    params.push(classId)
  }
  if (username) {
    sql += ' AND  `leave_user` = ?'
    params.push(username)
  }

  if (leave_type) {
    sql += ' AND `leave`.`leave_type` = ?'
    params.push(leave_type)
  }
  const { type: user_type, id } = req.user

  // 教师只能查看自己的
  if (user_type !== 0) {
    if ((user_type === 1 && type === '1') || (user_type === 2 && type === '2')) {
      sql += ' AND `leave`.`id` = ?'
      params.push(id)
    }
    if (user_type === 1 && type === '2') {
      // 判断是否为班主任
      let { classId } = req.user
      if (classId !== null) {
        sql += ' AND `leave`.`classId` = ?'
        params.push(classId)
      } else {
        return res.status(200).json(createResponse(200, ''))
      }
    }
  }

  connection.query(sql, params, function (error, results, fields) {
    if (error) {
      console.log(error)
      // 如果有错误，返回错误信息
      return res.status(500).json({ error: 'Error fetching classes' })
    }
    // 格式化时间字段
    results.forEach(result => {
      result.leave_start_date = moment(result.leave_start_date).format('YYYY-MM-DD HH:mm:ss')
      result.leave_end_date = moment(result.leave_end_date).format('YYYY-MM-DD HH:mm:ss')
    })
    return res.status(200).json(createResponse(200, '', results))
  })
})

// 添加请假信息
app.post('/api/v1/leave', authenticateToken, (req, res) => {
  const { reason, leave_type, leave_time } = req.body
  const { username, type, id, classId } = req.user

  // 处理结束时间为当天的 23:59:59
  const startDateTime = new Date(leave_time[0])
  startDateTime.setHours(0, 0, 0, 0) // 设置为当天的 00:00
  const endDateTime = new Date(leave_time[1])
  endDateTime.setHours(23, 59, 59, 0) // 设置为当天的 23:59:59

  const sql = 'INSERT INTO `leave` (reason, leave_type, leave_user, classId, id, approve_status, leave_start_date, leave_end_date, type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'
  const values = [reason, leave_type, username, classId, id, 0, startDateTime, endDateTime, type]

  connection.query(sql, values, (err, result) => {
    if (err) throw err
    return res.send(createResponse(200, '', result))
  })
})

// 审批请假
app.post('/api/v1/examine', authenticateToken, (req, res) => {
  // console.log(req.body)
  let { leave_id, approve_status } = req.body
  const { username } = req.user
  const sql = 'UPDATE `leave` SET approve = ?, approve_status = ? WHERE leave_id = ? '
  let values = [username, approve_status, leave_id]
  connection.query(sql, values, (err, result) => {
    if (err) throw err

    return res.send(createResponse(200, '', { msg: '审批成功' }))
  })
})

// 获取首页信息
app.get('/api/v1/dashboard', authenticateToken, (req, res) => {
  const { type, id, classId } = req.user
  let sql
  let values = []
  switch (type) {
    case 0:
      sql =
        'SELECT COUNT(*) AS `all`, SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS teacher_num,SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS student_num,SUM(CASE WHEN approve_status = 0 THEN 1 ELSE 0 END) AS approve_no,SUM(CASE WHEN `approve_status` <> 0 THEN 1 ELSE 0 END) AS approve_yes FROM `leave`'
      break
    case 1:
      sql = 'SELECT COUNT(*) AS `all`, SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS student_num,SUM(CASE WHEN approve_status = 0 THEN 1 ELSE 0 END) AS approve_no,SUM(CASE WHEN approve_status <> 0 THEN 1 ELSE 0 END) AS approve_yes FROM `leave` WHERE  type = 2  AND classId = ?'
      values = [classId]
      break
  }
  if (sql) {
    connection.query(sql, values, (err, result) => {
      if (err) throw err
      return res.send(createResponse(200, '', result[0]))
    })
  } else {
    return res.send(createResponse(200, ''))
  }
})

// 获取公告列表
app.get('/api/v1/bulletin', authenticateToken, (req, res) => {
  connection.query('SELECT bulletin_content, DATE_FORMAT(bulletin_time, "%Y-%m-%d %H:%i:%s") AS formatted_bulletin_time FROM bulletin', function (error, results) {
    if (error) throw error
    return res.send(createResponse(200, '', results))
  })
})
// 发布公告
app.post('/api/v1/bulletin', authenticateToken, (req, res) => {
  let { bulletin_content } = req.body
  // 获取当前时间作为bulletin_time
  const bulletin_time = format(new Date(), 'yyyy-MM-dd HH:mm:ss')
  // 插入bulletin到数据库
  const query = 'INSERT INTO bulletin (bulletin_content, bulletin_time) VALUES (?, ?)'
  let values = [bulletin_content, bulletin_time]
  connection.query(query, values, (error, result) => {
    return res.send(createResponse(200, '', result))
  })
})
// 示例接口 - 获取老师信息和今天的请假情况
app.get('/teachersLeaveToday', authenticateToken, (req, res) => {
  let { type, classId } = req.user
  let { role } = req.query
  if (type !== 2) {
    let query = role === 1 ? `SELECT user.* WHERE type = ${role}` : `SELECT user.*, class.className FROM user LEFT JOIN class ON user.classId = class.classId WHERE type = ${role}`
    // 查询 type 为 1 的老师信息
    connection.query(query, (err, teachers) => {
      if (err) {
        console.log(err)
        return res.status(500).json({ error: 'Internal Server Error' })
      }

      // 获取当天的日期时间范围
      const today = new Date()
      const startOfToday = new Date(today.getFullYear(), today.getMonth(), today.getDate(), 0, 0, 0)
      const endOfToday = new Date(today.getFullYear(), today.getMonth(), today.getDate(), 23, 59, 59)

      // 查询今天是否有老师请假
      connection.query('SELECT id, id FROM `leave` WHERE leave_start_date <= ? AND leave_end_date >= ?  AND approve_status = 1', [startOfToday, endOfToday], (err, leaveResults) => {
        if (err) {
          console.log(err)
          return res.status(500).json({ error: 'Internal Server Error' })
        }
        console.log(leaveResults, '11111')
        // 将请假结果转换为散列表方便查询
        const leaveMap = {}
        leaveResults.forEach(leave => {
          leaveMap[leave.id] = true
        })

        // 在老师信息中添加是否请假的标记
        let teachersWithLeaveInfo = teachers.map(teacher => {
          const isOnLeaveToday = leaveMap[teacher.id] ? true : false
          return { ...teacher, isOnLeaveToday }
        })
        // console.log(classId, teachersWithLeaveInfo)
        // 判断是否为班主任
        if (classId !== 1 && type === 1) {
          teachersWithLeaveInfo = teachersWithLeaveInfo.filter(item => {
            return item.classId === classId
          })
        }
        // 返回结果
        return res.send(createResponse(200, '', teachersWithLeaveInfo))
      })
    })
  } else {
    return res.send(createResponse(200, '', []))
  }
})
// 根据用户id查询用户密码
app.get('/api/v1/password_find', (req, res) => {
  let { id } = req.query

  let sql = 'SELECT * FROM user WHERE id = ?'
  connection.query(sql, [id], (error, result) => {
    return res.send(createResponse(200, '', result[0].password))
  })
})

// 根据用户id修改部分信息
app.post('/api/v1/edit_info', authenticateToken, (req, res) => {
  let { id, username, password, phone, sex, email } = req.body

  const sql = `UPDATE user SET username = ?, password = ?,  phone = ?, sex = ?, email = ? WHERE id = ?`
  const values = [username, password, phone, sex, email, id]
  connection.query(sql, values, (err, result) => {
    if (err) throw err
    console.log(req.user)
    return res.send(createResponse(200, '', '修改成功'))
  })
})

// 退出登录
app.delete('/api/v1/auth/logout', authenticateToken, (req, res) => {
  // 用户已通过身份验证中间件，现在是安全的执行退出逻辑
  // 你可以在这里记录用户退出登录的时间或其他相关操作
  // 但主要的工作是在客户端完成的，即销毁JWT
  // 发送成功响应给客户端
  return res.send(createResponse(200, '退出登录成功'))
})
app.listen(3000, () => {
  console.log('Server started on port 3000')
})
// connection.end((err) => {
//   if (err) {
//     console.error("Error closing MySQL database connection: " + err.stack);
//     return;
//   }
//   console.log("MySQL database connection closed");
// });

process.on('exit', () => {
  connection.end()
})
